IF EXISTS (SELECT * FROM sys.databases WHERE name='DrsagitnDB')
DROP DATABASE DrsagitnDB

GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name='DrsagitnDB')
CREATE DATABASE DrsagitnDB

GO
USE DrsagitnDB

GO
/*************************************************************************************************
	Table tgUser
	Name: tgUser
	Purpose: Contain all user in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgUser')
CREATE TABLE tgUser
(
	[UserID]		INT NOT NULL IDENTITY,
	[UserName]		VARCHAR(50) NOT NULL,
	[FullName]		VARCHAR(100),
	[Avatar]		VARCHAR(100),	-- Link den avatar
	[Password]		VARCHAR(255) NOT NULL,
	[Salt]			VARCHAR(50),
	[SignUpDate]	DATETIME,
	[LastVisit]		DATETIME,
	[Status]		VARCHAR(50)
	
	CONSTRAINT pk_tgUser_UserID PRIMARY KEY (UserID)	
)

GO


/*************************************************************************************************
	Table tgCategory
	Name: tgCategory
	Purpose: Category for product (Phan loai)
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgCategory')
CREATE TABLE tgCategory
(
	[CatID]				INT NOT NULL IDENTITY,
	[CatName]			NVARCHAR(200) NOT NULL,
	[CateID_Parent]		INT
	
	CONSTRAINT pk_tgCategory_CatID PRIMARY KEY (CatID)	
	
	CONSTRAINT fk_tgCategory_CatID_Parent FOREIGN KEY (CateID_Parent) REFERENCES tgCategory(CatID)	
)	

GO

/*************************************************************************************************
	Table tgProduct
	Name: tgProduct (San pham)
	Purpose: Contain all kind of products in the system
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgProduct')
CREATE TABLE tgProduct
(
	[ProductID]			INT NOT NULL IDENTITY,
	[ProductName]		NVARCHAR(255) NOT NULL,
	[Manufacturer]		NVARCHAR(50),
	[Describe]			NVARCHAR(255) NOT NULL,
	[Image]				NVARCHAR(100),
	[CatID]				INT,
	[ProductCode]		NVARCHAR(50)
	
	CONSTRAINT pk_tgProduct_ProductID PRIMARY KEY (ProductID)	
	
	CONSTRAINT pk_tgProduct_CatID FOREIGN KEY (CatID) REFERENCES tgCategory(CatID)
)	

GO
/*************************************************************************************************
	Table tgAttribute
	Name: tgAttribute
	Purpose: Attribute of product and category (Thuoc tinh)
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgAttribute')
CREATE TABLE tgAttribute
(
	[AttID]		INT NOT NULL IDENTITY,
	[AttributeName]		NVARCHAR(200) NOT NULL,
	[AttributeDataType]	NVARCHAR(200) NOT NULL
	
	CONSTRAINT pk_tgAttribute_AttID PRIMARY KEY (AttID)	
)	

GO

/*************************************************************************************************
	Table tgCategory_Attribute
	Name: tgCategory_Attribute
	Purpose: Attribute of category (PhanLoai_Thuoctinh)
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgCategory_Attribute')
CREATE TABLE tgCategory_Attribute
(
	[CatAID]	INT NOT NULL IDENTITY,
	[CatID]		INT,
	[AttID]		INT
	
	CONSTRAINT pk_tgCategory_Attribute_CatAID PRIMARY KEY (CatAID)	
)	

	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgCategory_Attribute_CatID')
	ALTER TABLE tgCategory_Attribute
	DROP CONSTRAINT fk_tgCategory_Attribute_CatID
	GO
	ALTER TABLE tgCategory_Attribute
	ADD CONSTRAINT fk_tgCategory_Attribute_CatID FOREIGN KEY (CatID) REFERENCES tgCategory(CatID)	
	
	GO
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgCategory_Attribute_AttID')
	ALTER TABLE tgCategory_Attribute
	DROP CONSTRAINT fk_tgCategory_Attribute_AttID
	GO
	ALTER TABLE tgCategory_Attribute
	ADD CONSTRAINT fk_tgCategory_Attribute_AttID FOREIGN KEY (AttID) REFERENCES tgAttribute(AttID)	

GO
/*************************************************************************************************
	Table tgAttributeDetail
	Name: tgAttributeDetail
	Purpose: Attribute detail (Chi tiet thuoc tinh)
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgAttributeDetail')
CREATE TABLE tgAttributeDetail
(
	[AttDID]		INT NOT NULL IDENTITY,
	[ProductID]		INT,
	[AttID]			INT,
	[AttDValue]		NVARCHAR(20)
	
	CONSTRAINT pk_tgAttributeDetail_AttDID PRIMARY KEY (AttDID)	
)	

	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgAttributeDetail_ProductID')
	ALTER TABLE tgAttributeDetail
	DROP CONSTRAINT fk_tgAttributeDetail_ProductID
	GO
	ALTER TABLE tgAttributeDetail
	ADD CONSTRAINT fk_tgAttributeDetail_ProductID FOREIGN KEY (ProductID) REFERENCES tgProduct(ProductID)	
	
	GO
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgAttributeDetail_AttID')
	ALTER TABLE tgAttributeDetail
	DROP CONSTRAINT fk_tgAttributeDetail_AttID
	GO
	ALTER TABLE tgAttributeDetail
	ADD CONSTRAINT fk_tgAttributeDetail_AttID FOREIGN KEY (AttID) REFERENCES tgAttribute(AttID)	
GO
/*************************************************************************************************
	Table tgProductRanking
	Name: tgProductRanking
	Purpose: Ranking for product
*************************************************************************************************/
--IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgProductRanking')
--CREATE TABLE tgProductRanking
--(
--	[PRID]			INT NOT NULL IDENTITY,
--	[ProductID]		INT NOT NULL,
--	[LikeNo]		INT,
--	[DislikeNo]		INT,
--	[DateRate]		DATETIME	-- Dung de thong ke rating cao nhat theo thoi gian
	
--	CONSTRAINT pk_tgProductRanking_PRID PRIMARY KEY (PRID)	
	
--	CONSTRAINT fk_tgProductRanking_tgProduct_ProductID FOREIGN KEY(ProductID) REFERENCES tgProduct(ProductID)
--)	

--GO

/*************************************************************************************************
	Table tgUser_Product
	Name: tgUser_Product
	Purpose: To determine if user liked one product or not
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgUser_Product')
CREATE TABLE tgUser_Product
(
	[UserID]		INT NOT NULL,
	[ProductID]		INT NOT NULL,
	[Status]		BIT, -- 1: Like, -1: Dislike
	[DateRate]		DATETIME
	
	CONSTRAINT pk_tgUser_Product PRIMARY KEY (UserID, ProductID)	
	
	CONSTRAINT fk_tgUser_Product_tgUser_UserID FOREIGN KEY(UserID) REFERENCES tgUser(UserID)
)	
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgUser_Product_ProductID')
	ALTER TABLE tgUser_Product
	DROP CONSTRAINT fk_tgUser_Product_ProductID
	GO
	ALTER TABLE tgUser_Product
	ADD CONSTRAINT fk_tgUser_Product_ProductID FOREIGN KEY (ProductID) REFERENCES tgProduct(ProductID)	
	
GO
/*************************************************************************************************
	Table tgProductView
	Name: tgProductView
	Purpose: Viewing of product
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgProductView')
CREATE TABLE tgProductView
(
	[PVID]			INT NOT NULL IDENTITY,
	[ProductID]		INT NOT NULL,
	[ViewNo]		INT
	--[DateView]		DATETIME	-- Dung de thong ke most view
	
	CONSTRAINT pk_tgProductView_PVID PRIMARY KEY (PVID)	
	
	CONSTRAINT fk_tgProductView_tgProduct_ProductID FOREIGN KEY(ProductID) REFERENCES tgProduct(ProductID)
)	

GO

/*************************************************************************************************
	Table tgComment
	Name: tgComment
	Purpose: Contain all comments for question 
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgComment')
CREATE TABLE tgComment
(
	[CMID]			INT NOT NULL IDENTITY,
	[Title]			NVARCHAR(100) NOT NULL,
	[CommentDate]	DATETIME,	
	[Content]		NTEXT NOT NULL,
	[UserID]		INT,	
	[ProductID]		INT,	
	[CMID_Parent]	INT
	
	CONSTRAINT pk_tgComment_CMID PRIMARY KEY (CMID)	
)
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgComment_ParentComment')
	ALTER TABLE tgComment
	DROP CONSTRAINT fk_tgComment_ParentComment
	GO
	ALTER TABLE tgComment
	ADD CONSTRAINT fk_tgComment_ParentComment FOREIGN KEY (CMID_Parent) REFERENCES tgComment(CMID)	
	GO
	
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgComment_tgUser')
	ALTER TABLE tgComment
	DROP CONSTRAINT fk_tgComment_tgUser
	GO
	ALTER TABLE tgComment
	ADD CONSTRAINT fk_tgComment_tgUser FOREIGN KEY (UserID) REFERENCES tgUser(UserID)
	
	GO
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgComment_tgProduct')
	ALTER TABLE tgComment
	DROP CONSTRAINT fk_tgComment_tgProduct
	GO
	ALTER TABLE tgComment
	ADD CONSTRAINT fk_tgComment_tgProduct FOREIGN KEY (ProductID) REFERENCES tgProduct(ProductID)
	
	
GO

/*************************************************************************************************
	Table tgCommentRanking
	Name: tgCommentRanking
	Purpose: Ranking for comment
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgCommentRanking')
CREATE TABLE tgCommentRanking
(
	[CRID]			INT NOT NULL IDENTITY,
	[CMID]			INT NOT NULL,
	[LikeNo]		INT,
	[DislikeNo]		INT
	
	CONSTRAINT pk_tgCommentRanking_CRID PRIMARY KEY (CRID)	
	
	CONSTRAINT fk_tgCommentRanking_tgComment_CMID FOREIGN KEY(CMID) REFERENCES tgComment(CMID)
)	

GO

/*************************************************************************************************
	Table tgUser_Comment
	Name: tgUser_Comment
	Purpose: To determine if user liked a comment or not
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgUser_Comment')
CREATE TABLE tgUser_Comment
(
	[UserID]		INT NOT NULL,
	[CMID]			INT NOT NULL,
	[Status]		BIT
	
	CONSTRAINT pk_tgUser_Comment PRIMARY KEY (UserID, CMID)	
	
	CONSTRAINT fk_tgUser_Comment_tgUser_UserID FOREIGN KEY(UserID) REFERENCES tgUser(UserID)
)	
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgUser_Comment_CMID')
	ALTER TABLE tgUser_Comment
	DROP CONSTRAINT fk_tgUser_Comment_CMID
	GO
	ALTER TABLE tgUser_Comment
	ADD CONSTRAINT fk_tgUser_Comment_CMID FOREIGN KEY (CMID) REFERENCES tgComment(CMID)	
	
GO

/*************************************************************************************************
	Table tgProductPrice
	Name: tgProductPrice
	Purpose: To track of changing product's price
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgProductPrice')
CREATE TABLE tgProductPrice
(
	[PriceID]		INT NOT NULL IDENTITY,
	[ProductID]		INT NOT NULL,
	[ChangeDate]	DATETIME,
	[Price]			FLOAT
	
	CONSTRAINT pk_tgProductPrice PRIMARY KEY (PriceID)	
	
	CONSTRAINT fk_tgProductPrice_tgProduct FOREIGN KEY(ProductID) REFERENCES tgProduct(ProductID)
)	
	
GO

/*************************************************************************************************
	Table tgPaper
	Name: tgPaper
	Purpose: Contain all paper about  product or category.
*************************************************************************************************/
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name='tgPaper')
CREATE TABLE tgPaper
(
	[PaperID]		INT NOT NULL IDENTITY,
	[Title]			NVARCHAR(100) NOT NULL,
	[PaperDate]		DATETIME,	
	[Content]		NTEXT NOT NULL,
	[UserID]		INT,	
	[ProductID]		INT
	
	CONSTRAINT pk_tgPaper_PaperID PRIMARY KEY (PaperID)	
)
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgPaper_tgProduct')
	ALTER TABLE tgPaper
	DROP CONSTRAINT fk_tgPaper_tgProduct
	GO
	ALTER TABLE tgPaper
	ADD CONSTRAINT fk_tgPaper_ProductID FOREIGN KEY (ProductID) REFERENCES tgProduct(ProductID)	
	GO
	
	IF EXISTS (SELECT * FROM sys.objects WHERE name='fk_tgPaper_tgUser')
	ALTER TABLE tgPaper
	DROP CONSTRAINT fk_tgPaper_tgUser
	GO
	ALTER TABLE tgPaper
	ADD CONSTRAINT fk_tgPaper_tgUser FOREIGN KEY (UserID) REFERENCES tgUser(UserID)
	
GO
